LiveSurfacePerf

V8 Message Definiton

LiveSurfacePerf records contain current and prior period implied ATM volatilities and greeks and as well as fixed-strike PnL values. The strike used for the fix-strike calculation is equal to the forward underlier price that prevailed on the open. Note that this strike price 'resets' each day.\nLiveSurfacePerf records are published to the SpiderRock elastic cluster at the end of the day for each option expiration. These records are designed to allow fixed strike EOD to EOD attributed PnL to be easily calculated. These records can also be used to measure atm volatility dynamics.

METADATA

Attribute	Value
Topic	1000-analytics
MLink Token	SystemData
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
ekey_at	enum - AssetType	PRI	'None'
ekey_ts	enum - TickerSrc	PRI	'None'
ekey_tk	VARCHAR(12)	PRI	''
ekey_yr	SMALLINT UNSIGNED	PRI	0
ekey_mn	TINYINT UNSIGNED	PRI	0
ekey_dy	TINYINT UNSIGNED	PRI	0
perfSurfType	enum - PerfSurfaceType	PRI	'None'	Preferred surface type None Live PriorDay
date	VARCHAR(10)		''
time	VARCHAR(8)		''
ticker_at	enum - AssetType		'None'	SpiderRock underlier stock key
ticker_ts	enum - TickerSrc		'None'	SpiderRock underlier stock key
ticker_tk	VARCHAR(12)		''	SpiderRock underlier stock key
exType	enum - ExerciseType		'None'
modelType	enum - CalcModelType		'None'
uMark	FLOAT		0	current underlier mark usually mid market
years	FLOAT		0	time to expiration in years
rate	FLOAT		0	interest rate
sdiv	FLOAT		0	stock dividend borrow rate
ddiv	FLOAT		0	present value of discrete dividend stream
eventCnt	FLOAT		0	number of qualifying earnings or earnings like events between now and expiration can be fractional
iEMove	FLOAT		0	implied event move if any
hEMove	FLOAT		0	average of the trailing 12 historical quarterly earnings moves if any
strike	FLOAT		0	atm strike uMark MathExpyears rate ddiv
cIVol	FLOAT		0	call surface atm vol strike uMark
pIVol	FLOAT		0	put surface atm vol strike uMark
cSOpx	FLOAT		0	call option price cAtm uMark years sdiv rate ddivstrike uMark
pSOpx	FLOAT		0	put option price pAtm uMark years sdiv rate ddivstrike uMark
cDe	FLOAT		0	surface call delta cAtm uMark years sdiv rate ddiv
cGa	FLOAT		0	surface call gamma cAtm uMark years sdiv rate ddiv
cTh	FLOAT		0	surface call theta cAtm uMark years sdiv rate ddiv
cVe	FLOAT		0	surface call vega cAtm uMark years sdiv rate ddiv
cRo	FLOAT		0	surface call rho cAtm uMark years sdiv rate ddiv
pDe	FLOAT		0	surface put delta pAtm uMark years sdiv rate ddiv
pGa	FLOAT		0	surface put gamma pAtm uMark years sdiv rate ddiv
pTh	FLOAT		0	surface put theta pAtm uMark years sdiv rate ddiv
pVe	FLOAT		0	surface put vega pAtm uMark years sdiv rate ddiv
pRo	FLOAT		0	surface put rho pAtm uMark years sdiv rate ddiv
xxCnt	TINYINT UNSIGNED		0	number of valid surface strikes
pwidth	FLOAT		0	minimum mkt premium width
vwidth	FLOAT		0	minimum mkt volatility width
fixCIVol	FLOAT		0	fixed strike call surface atm vol strike prvUMark
fixPIVol	FLOAT		0	fixed strike put surface atm vol strike prvUMark
fixCSOpx	FLOAT		0	fixed strike call option price fixCAtm uMark years sdiv rate ddivstrike prvUMark
fixPSOpx	FLOAT		0	fixed strike put option price fixPAtm uMark years sdiv rate ddivstrike prvUMark
prvUMark	FLOAT		0	prior period underlier mark
prvYears	FLOAT		0	prior period years to expiration
prvSDiv	FLOAT		0	prior period sdiv
prvRate	FLOAT		0	prior period rate
prvDDiv	FLOAT		0	prior period ddiv
prvStrike	FLOAT		0	prior period atm strike prvUMark MathExpyears rate ddiv
prvEventCnt	FLOAT		0	number of qualifying earnings or earnings like events between now and expiration can be fractional
prvIEMove	FLOAT		0	prior period implied event move if any
prvCIVol	FLOAT		0	prior period surface cAtm strike prvMark
prvPIVol	FLOAT		0	prior period surface pAtm strike prvMark
prvCSOpx	FLOAT		0	prior period call surface price strike prvUMark
prvPSOpx	FLOAT		0	prior period put surface price strike prvUMark
prvCDe	FLOAT		0	prior period surface call delta cAtm uMark years sdiv rate ddiv
prvCGa	FLOAT		0	prior period surface call gamma cAtm uMark years sdiv rate ddiv
prvCTh	FLOAT		0	prior period surface call theta cAtm uMark years sdiv rate ddiv
prvCVe	FLOAT		0	prior period surface call vega cAtm uMark years sdiv rate ddiv
prvCRo	FLOAT		0	prior period surface call rho cAtm uMark years sdiv rate ddiv
prvPDe	FLOAT		0	prior period surface put delta pAtm uMark years sdiv rate ddiv
prvPGa	FLOAT		0	prior period surface put gamma pAtm uMark years sdiv rate ddiv
prvPTh	FLOAT		0	prior period surface put theta pAtm uMark years sdiv rate ddiv
prvPVe	FLOAT		0	prior period surface put vega pAtm uMark years sdiv rate ddiv
prvPRo	FLOAT		0	prior period surface put rho pAtm uMark years sdiv rate ddiv
prvXXCnt	TINYINT UNSIGNED		0	prior period number of valid surface strikes
prvPWidth	FLOAT		0	prior period minimum mkt premium width
prvVWidth	FLOAT		0	prior period minimum mkt volatility width
cOptPnL	FLOAT		0	call option pnl fixCOpx prvCOpx
pOptPnL	FLOAT		0	put option pnl fixPOpx prvPOpx
dUPrc	FLOAT		0	uMark prvUMark
cDePnL	FLOAT		0	prvCDe dUPrc
cGaPnL	FLOAT		0	05 prvCGa dUPrc dUPrc
cThPnL	FLOAT		0	prvCTh years prvYears
cVePnL	FLOAT		0	prvCVe sVol prvSVol
cRoPnL	FLOAT		0	prvCRo sdiv prvSDiv
pDePnL	FLOAT		0	prvPDe dUPrc
pGaPnL	FLOAT		0	05 prvPGa dUPrc dUPrc
pThPnL	FLOAT		0	prvPTh years prvYears
pVePnL	FLOAT		0	prvPVe sVol prvSVol
pRoPnL	FLOAT		0	prvPRo sdiv prvSDiv
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ekey_tk	1
ekey_yr	2
ekey_mn	3
ekey_dy	4
ekey_at	5
ekey_ts	6
perfSurfType	7

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgLiveSurfacePerf` (
    `ekey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ekey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ekey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `ekey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `ekey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `ekey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `perfSurfType` ENUM('None','Live','PriorDay') NOT NULL DEFAULT 'None' COMMENT 'Preferred surface type: None; Live; PriorDay',
    `date` VARCHAR(10) NOT NULL DEFAULT '',
    `time` VARCHAR(8) NOT NULL DEFAULT '',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock underlier stock key',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'SpiderRock underlier stock key',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'SpiderRock underlier stock key',
    `exType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None',
    `modelType` ENUM('None','LogNormalExact','NormalExact','LogNormalApprox','NormalApprox') NOT NULL DEFAULT 'None',
    `uMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'current underlier mark (usually mid market)',
    `years` FLOAT NOT NULL DEFAULT 0 COMMENT 'time to expiration (in years)',
    `rate` FLOAT NOT NULL DEFAULT 0 COMMENT 'interest rate',
    `sdiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'stock dividend (borrow rate)',
    `ddiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'present value of discrete dividend stream',
    `eventCnt` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of qualifying earnings (or earnings like) events (between now and expiration) [can be fractional]',
    `iEMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'implied event move (if any)',
    `hEMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'average of the trailing 12 historical (quarterly) earnings moves (if any)',
    `strike` FLOAT NOT NULL DEFAULT 0 COMMENT 'atm strike = uMark * Math.Exp(years * rate) - ddiv;',
    `cIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'call surface atm vol @ strike = uMark',
    `pIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'put surface atm vol @ strike = uMark',
    `cSOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'call option price @ (cAtm, uMark, years, sdiv, rate, ddiv);strike = uMark',
    `pSOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'put option price @ (pAtm, uMark, years, sdiv, rate, ddiv);strike = uMark',
    `cDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface call delta @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `cGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface call gamma @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `cTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface call theta @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `cVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface call vega @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `cRo` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface call rho @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `pDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface put delta @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `pGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface put gamma @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `pTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface put theta @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `pVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface put vega @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `pRo` FLOAT NOT NULL DEFAULT 0 COMMENT 'surface put rho @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `xxCnt` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'number of valid surface strikes',
    `pwidth` FLOAT NOT NULL DEFAULT 0 COMMENT 'minimum mkt premium width',
    `vwidth` FLOAT NOT NULL DEFAULT 0 COMMENT 'minimum mkt volatility width',
    `fixCIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'fixed strike call surface atm vol @ strike = prvUMark',
    `fixPIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'fixed strike put surface atm vol @ strike = prvUMark',
    `fixCSOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'fixed strike call option price @ (fixCAtm, uMark, years, sdiv, rate, ddiv);strike = prvUMark',
    `fixPSOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'fixed strike put option price @ (fixPAtm, uMark, years, sdiv, rate, ddiv);strike = prvUMark',
    `prvUMark` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period underlier mark',
    `prvYears` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period years to expiration',
    `prvSDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period sdiv',
    `prvRate` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period rate',
    `prvDDiv` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period ddiv',
    `prvStrike` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period atm strike = prvUMark * Math.Exp(years * rate) - ddiv;',
    `prvEventCnt` FLOAT NOT NULL DEFAULT 0 COMMENT 'number of qualifying earnings (or earnings like) events (between now and expiration) [can be fractional]',
    `prvIEMove` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period implied event move (if any)',
    `prvCIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface cAtm @ strike = prvMark',
    `prvPIVol` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface pAtm @ strike = prvMark',
    `prvCSOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period call surface price @ strike = prvUMark',
    `prvPSOpx` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period put surface price @ strike = prvUMark',
    `prvCDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface call delta @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `prvCGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface call gamma @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `prvCTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface call theta @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `prvCVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface call vega @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `prvCRo` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface call rho @ (cAtm, uMark, years, sdiv, rate, ddiv)',
    `prvPDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface put delta @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `prvPGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface put gamma @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `prvPTh` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface put theta @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `prvPVe` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface put vega @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `prvPRo` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period surface put rho @ (pAtm, uMark, years, sdiv, rate, ddiv)',
    `prvXXCnt` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'prior period number of valid surface strikes',
    `prvPWidth` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period minimum mkt premium width',
    `prvVWidth` FLOAT NOT NULL DEFAULT 0 COMMENT 'prior period minimum mkt volatility width',
    `cOptPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'call option pnl (fixCOpx - prvCOpx)',
    `pOptPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'put option pnl (fixPOpx - prvPOpx)',
    `dUPrc` FLOAT NOT NULL DEFAULT 0 COMMENT 'uMark - prvUMark',
    `cDePnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvCDe * dUPrc',
    `cGaPnL` FLOAT NOT NULL DEFAULT 0 COMMENT '0.5 * prvCGa * dUPrc * dUPrc',
    `cThPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvCTh * (years - prvYears)',
    `cVePnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvCVe * (sVol - prvSVol)',
    `cRoPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvCRo * (sdiv - prvSDiv)',
    `pDePnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvPDe * dUPrc',
    `pGaPnL` FLOAT NOT NULL DEFAULT 0 COMMENT '0.5 * prvPGa * dUPrc * dUPrc',
    `pThPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvPTh * (years - prvYears)',
    `pVePnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvPVe * (sVol - prvSVol)',
    `pRoPnL` FLOAT NOT NULL DEFAULT 0 COMMENT 'prvPRo * (sdiv - prvSDiv)',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ekey_tk`,`ekey_yr`,`ekey_mn`,`ekey_dy`,`ekey_at`,`ekey_ts`,`perfSurfType`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='LiveSurfacePerf records contain current and prior period implied ATM volatilities and greeks and as well as fixed-strike PnL values.  The strike used for the fix-strike calculation is equal to the forward underlier price that prevailed on the open.  Note that this strike price \'resets\' each day.\nLiveSurfacePerf records are published to the SpiderRock elastic cluster at the end of the day for each option expiration.  These records are designed to allow fixed strike EOD to EOD attributed PnL to be easily calculated.  These records can also be used to measure atm volatility dynamics.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ekey_at`,
    `ekey_ts`,
    `ekey_tk`,
    `ekey_yr`,
    `ekey_mn`,
    `ekey_dy`,
    `perfSurfType`,
    `date`,
    `time`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `exType`,
    `modelType`,
    `uMark`,
    `years`,
    `rate`,
    `sdiv`,
    `ddiv`,
    `eventCnt`,
    `iEMove`,
    `hEMove`,
    `strike`,
    `cIVol`,
    `pIVol`,
    `cSOpx`,
    `pSOpx`,
    `cDe`,
    `cGa`,
    `cTh`,
    `cVe`,
    `cRo`,
    `pDe`,
    `pGa`,
    `pTh`,
    `pVe`,
    `pRo`,
    `xxCnt`,
    `pwidth`,
    `vwidth`,
    `fixCIVol`,
    `fixPIVol`,
    `fixCSOpx`,
    `fixPSOpx`,
    `prvUMark`,
    `prvYears`,
    `prvSDiv`,
    `prvRate`,
    `prvDDiv`,
    `prvStrike`,
    `prvEventCnt`,
    `prvIEMove`,
    `prvCIVol`,
    `prvPIVol`,
    `prvCSOpx`,
    `prvPSOpx`,
    `prvCDe`,
    `prvCGa`,
    `prvCTh`,
    `prvCVe`,
    `prvCRo`,
    `prvPDe`,
    `prvPGa`,
    `prvPTh`,
    `prvPVe`,
    `prvPRo`,
    `prvXXCnt`,
    `prvPWidth`,
    `prvVWidth`,
    `cOptPnL`,
    `pOptPnL`,
    `dUPrc`,
    `cDePnL`,
    `cGaPnL`,
    `cThPnL`,
    `cVePnL`,
    `cRoPnL`,
    `pDePnL`,
    `pGaPnL`,
    `pThPnL`,
    `pVePnL`,
    `pRoPnL`,
    `timestamp`
FROM `SRAnalytics`.`MsgLiveSurfacePerf`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ekey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ekey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ekey_tk` = 'Example_ekey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `ekey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `ekey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `ekey_dy` = 1
  AND
    /* Replace with a ENUM('None','Live','PriorDay') */ 
    `perfSurfType` = 'None';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='LiveSurfacePerf' ORDER BY ordinal_position ASC;